//
//  TodaysPlanDataModel.m
//  TodaysPlan
//
//  Created by 寺田 篤史 on 11/09/26.
//  Copyright (c) 2011 __MyCompanyName__. All rights reserved.
//

#import "TodaysPlanDataModel.h"
#import "SqliteDB.h"
#import <sqlite3.h>
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
#import "ConfigManager.h"


@implementation TodaysPlanDataModel

@synthesize recordId, gender, userName;

-(id) init {
    recordId = -1;
    return self;
}

+(int) countAll {
    NSString* dbPath = [SqliteDB getDatabaseFilePath];
    NSString* sql1 = [NSString stringWithFormat:@"select count(*) as count from Account"];
    
    FMDatabase* db = [FMDatabase databaseWithPath:dbPath];
    if(![db open]) {
        return 0;
    }
    [db setShouldCacheStatements:YES];
    
    FMResultSet* rs = nil;
    rs = [db executeQuery:sql1];
    
    int count = 0;
    if ([rs next]) {
        // just print out what we've got in a number of formats.
        count = [rs intForColumn:@"count"];
    }
    
    [rs close];
    [db close];
    
    return count;
}

/**
 *    select all
 */
+(NSMutableArray*)selectAll {
    NSString* dbPath = [SqliteDB getDatabaseFilePath];
    NSString* sql1 = [NSString stringWithFormat:@"select * from Account order by userName, gender, id"];
    NSMutableArray* ret = [[[NSMutableArray alloc] init] autorelease];
    
    FMDatabase* db = [FMDatabase databaseWithPath:dbPath];
    if(![db open]) {
        return nil;
    }
    [db setShouldCacheStatements:YES];
    
    FMResultSet* rs = nil;
    rs = [db executeQuery:sql1];
    
    while ([rs next]) {
        TodaysPlanDataModel* account = [TodaysPlanDataModel createAccountModel:rs];
        [ret addObject:account];
    }
    
    [rs close];
    [db close];
    
    return ret;
}

/**
 * select by id
 */
+(TodaysPlanDataModel*)selectById:(int)recordId {
    NSString* dbPath = [SqliteDB getDatabaseFilePath];
    DLog("dbPath=%@",dbPath);

    NSString* sql = [NSString stringWithFormat:@"select * from account where id = ?"];
    
    FMDatabase* db = [FMDatabase databaseWithPath:dbPath];
    if(![db open]) {
        return nil;
    }
    [db setShouldCacheStatements:YES];
    
    FMResultSet* rs = nil;
    rs = [db executeQuery:sql, [NSNumber numberWithInt:recordId]];
    
    TodaysPlanDataModel* account = nil;
    if ([rs next]) {
        account = [TodaysPlanDataModel createAccountModel:rs];
    }
    
    [rs close];
    [db close];
    
    return account;
}

+(TodaysPlanDataModel*) createAccountModel:(FMResultSet*)rs {
    TodaysPlanDataModel* account = [[[TodaysPlanDataModel alloc] init] autorelease];
    
    account.recordId = [rs intForColumn:@"id"];
    account.gender = [rs intForColumn:@"gender"];
    account.userName = [rs stringForColumn:@"userName"];
    
    return account;
}

/**
 *    save record
 */
+(int) saveId:(int)recordId gender:(int)gender userName:(NSString*)userName {
    NSString* dbPath = [SqliteDB getDatabaseFilePath];
    NSString* sql = [NSString stringWithFormat:@"%@%@",
                     @"insert or replace into Account (id, gender, userName)",
                     @" values (?, ?, ?)"];
    
    FMDatabase* db = [FMDatabase databaseWithPath:dbPath];
    if(![db open]) {
        return -1;
    }
    [db setShouldCacheStatements:YES];
    
    if (recordId == -1) {
        [db executeUpdate:sql, [NSNull null], [NSNumber numberWithInt:gender], userName];
    } else {
        [db executeUpdate:sql, [NSNumber numberWithInt:recordId], [NSNumber numberWithInt:gender], userName];
    }
    
    int lastInsertRowId = [db lastInsertRowId];
    [db close];
    
    return lastInsertRowId;
}

+(void) deleteId:(int)recordId {
    NSString* dbPath = [SqliteDB getDatabaseFilePath];
    NSString* sql = @"delete from Account where id = ?";
    
    FMDatabase* db = [FMDatabase databaseWithPath:dbPath];
    if(![db open]) {
        return;
    }
    [db setShouldCacheStatements:YES];
    
    [db executeUpdate:sql, [NSNumber numberWithInt:recordId]];
    
    [db close];
}


/**
 *    insert todays_plan
 */
+ (void)insertTodaysPlan:(NSString *)registDate task:(NSString *)task memo:(NSString *)memo
                priority:(NSString *)priority reminder:(int)reminder reminderDate:(NSString *)reminderDate {
    NSString *doneFlg = @"0";
    NSDateFormatter *createDateFormatter = [[[NSDateFormatter alloc] init] autorelease];
    [createDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    NSString* createDateStr = [createDateFormatter stringFromDate:[NSDate date]];
    
    NSString *dbPath = [SqliteDB getDatabaseFilePath];
    NSString *sql = [NSString stringWithFormat:@"%@%@",
                     @"insert into todays_plan(registdate, task, memo, priority, reminder, reminderdate, done, createdate)",
                     @" values (?, ?, ?, ?, ?, ?, ?, ?)"];
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    [db open];
    [db setShouldCacheStatements:YES];
    [db executeUpdate:sql, [NSString stringWithFormat:registDate], 
     [NSString stringWithFormat:task], 
     [NSString stringWithFormat:memo], 
     [NSString stringWithFormat:priority],
     [NSNumber numberWithInt:reminder],
     [NSString stringWithFormat:reminderDate], 
     [NSString stringWithFormat:doneFlg], 
     [NSString stringWithFormat:createDateStr]];
    
    if ([db hadError]) {
        DLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }    
    [db close];
}
/**
 *    update todays_plan
 */
+ (void)updateTodaysPlan:(int)pkey task:(NSString *)task memo:(NSString *)memo
                priority:(NSString *)priority reminder:(int)reminder reminderDate:(NSString *)reminderDate done:(NSString *)done {
    NSString *dbPath = [SqliteDB getDatabaseFilePath];
    NSString *sql = [NSString stringWithFormat:@"%@%@",
                     @"update todays_plan set task = ?, memo = ?, priority = ?, reminder = ?, reminderdate = ?, done = ?",
                     @" where id = ?"];
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    [db open];
    [db setShouldCacheStatements:YES];
    [db executeUpdate:sql, [NSString stringWithFormat:task], 
     [NSString stringWithFormat:memo],
     [NSString stringWithFormat:priority],
     [NSNumber numberWithInt:reminder],
     [NSString stringWithFormat:reminderDate],
     [NSString stringWithFormat:done],
     [NSNumber numberWithInt:pkey]];
    if ([db hadError]) {
        DLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }    
    [db close];
}

/**
 *    delete todays_plan
 */
+ (void)deleteTodaysPlan:(int)pkey{
    NSString *dbPath = [SqliteDB getDatabaseFilePath];
    NSString *sql = @"delete from todays_plan where id = ?";
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    [db open];
    [db setShouldCacheStatements:YES];
    [db executeUpdate:sql, [NSNumber numberWithInt:pkey]];
    if ([db hadError]) {
        DLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }    
    [db close];
}
+(int)countTodaysPlan:(NSString*)today {
    NSString *dbPath = [SqliteDB getDatabaseFilePath];
    NSString *sql = @"select count(*) as count from todays_plan where done = 0 and registdate = ?";
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    [db open];
    [db setShouldCacheStatements:YES];

    FMResultSet* rs = nil;
    rs = [db executeQuery:sql, [NSString stringWithFormat:today]];
    
    int count = 0;
    if ([rs next]) {
        // just print out what we've got in a number of formats.
        count = [rs intForColumn:@"count"];
    }
    
    [rs close];
    [db close];
    
    return count;
}

- (void)dealloc {
}

@end
